SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



--exec p_msdeptstartacc 2012,04,-99
alter         procedure p_msdeptstartacc (
  @nyear int,
  @nmonth int,
  @filid int 
)  as  
  declare @deptid int ,@nfilid int,@subjectid int,@moneyid int
  declare @bmbala  numeric(18,2)
  declare @fbmbala numeric(18,2)
  declare @tmsum   numeric(18,2)
  declare @ftmsum  numeric(18,2)
  declare @local_tab table(deptid integer,
                           subjectid integer,
                           moneyid integer,
                           bmbala numeric(18,2),
                           fbmbala numeric(18,2))

begin
  set nocount on
  delete tft_msdept where nyear=@nyear and nmonth=@nmonth  
  declare cur_dept cursor for
  select * from @local_tab
  declare cur_fil cursor for   select fi_filid from td_filiale where fi_filid=@filid or @filid =-99
  
  open cur_fil 
  fetch next from  cur_fil
  into  @nfilid;
  while @@fetch_status = 0 
  begin   
    insert into tft_msdept (deptid,subjectid,moneyid,nyear,nmonth,filid,tmsumdebit,tmsumcredit,tmsum,
      ftmsumdebit,ftmsumcredit,ftmsum)
    select d.deptid,d.subjectid,d.moneyid,@nyear,@nmonth,@nfilid,
      sum(dcdebitamt) tmsumdebit,sum(dccreditamt) tmsumcredit, sum((dcdebitamt-dccreditamt)*sign(abs(m.formertype-2002))) tmsum,
      sum(debitamt) ftmsumdebit,sum(creditamt) ftmsumcredit, sum((debitamt-creditamt)*sign(abs(m.formertype-2002))) ftmsum
    from tfb_voucherdtl d
    inner join tfb_voucher m on m.voucherid=d.voucherid and m.filid=@nfilid
    inner join tfd_subject s on d.subjectid=s.subjectid
    where s.dodept=1
      and d.deptid is not null
      and (d.voucherid<0 and (d.borderno=2 or d.borderno=3))
      group by  d.subjectid,d.deptid,d.moneyid
    delete from @local_tab
    insert into @local_tab
    select d.deptid,d.subjectid,d.moneyid,sum((s.isdebit*2-1)*(dcdebitamt+dccreditamt)) bmbala,
        sum((s.isdebit*2-1)*(debitamt+creditamt)) fbmbala
    from tfb_voucherdtl d
    inner join tfb_voucher m on m.voucherid=d.voucherid and m.filid=@nfilid
    inner join tfd_subject s on d.subjectid=s.subjectid
    where s.dodept=1 and d.deptid is not null
          and (d.voucherid<0 and d.borderno=1)
    group by  d.subjectid,d.deptid,d.moneyid

    open  cur_dept
    fetch next from  cur_dept
    into  @deptid,@subjectid,@moneyid,@bmbala,@fbmbala
    while @@fetch_status = 0
      begin
        update tft_msdept set bmbala =@bmbala,fbmbala=@fbmbala
        where deptid = @deptid and subjectid = @subjectid and moneyid=@moneyid
              and nyear=@nyear and nmonth=@nmonth  and filid=@nfilid
        fetch next from  cur_dept
        into  @deptid,@subjectid,@moneyid,@bmbala,@fbmbala
      end
    close  cur_dept

    delete from @local_tab
    insert into @local_tab
    select d.deptid,d.subjectid,d.moneyid,sum(dcdebitamt) tmsum, sum(debitamt) ftmsum
    from tfb_voucherdtl d
    inner join tfb_voucher m on m.voucherid=d.voucherid and m.filid=@nfilid
    inner join tfd_subject s on d.subjectid=s.subjectid
    where s.dodept=1 and d.deptid is not null
          and (d.voucherid<0 and d.borderno=4)
    group by  d.subjectid,d.deptid,d.moneyid

    open  cur_dept
    fetch next from  cur_dept
    into  @deptid,@subjectid,@moneyid,@tmsum,@ftmsum
    while @@fetch_status = 0
      begin
        update tft_msdept set tmsum = @tmsum,ftmsum = @ftmsum
        where deptid = @deptid and subjectid = @subjectid and moneyid=@moneyid
              and nyear=@nyear and nmonth=@nmonth  and filid=@nfilid
        fetch next from  cur_dept
        into  @deptid,@subjectid,@moneyid,@tmsum,@ftmsum
      end
    close  cur_dept
    fetch next from  cur_fil
    into  @nfilid;
 end -- end filid
 deallocate  cur_dept
 close cur_fil;
 deallocate  cur_fil;
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

